#!/bin/sh

if [ -n "$1" ]; then
    data_date=$1
else
  data_date=$(date -d "-1 days" +%F)
fi

LOAD_DATA_DIM_PROVINCE="
insert overwrite table yk_jtp_app_dim.dim_md_areas partition (dt = '${data_date}')
select *
from (with area as (select id,
                           areaname,
                           parentid,
                           bd09_lng,
                           bd09_lat
                    from yk_jtp_app_ods.ods_province
                    where dt='${data_date}'),
           t1 as (select id,
                         areaname
                  from area
                  where parentid = 0),
           t2 as (select area.id       as id,
                         t1.areaname   as province,
                         area.areaname as city
                  from t1
                           left join area
                                     on t1.id = area.parentid),
           t3 as (select area.id       as id,
                         t2.province,
                         t2.city,
                         area.areaname as district
                  from t2
                           left join area
                                     on t2.id = area.parentid)
      select area.id       as id,
             province,
             city,
             district,
             area.areaname as street,
             area.bd09_lng,
             area.bd09_lat,
             cottt(area.bd09_lat, area.bd09_lng, 6)
      from t3
               left join area
                         on t3.id = area.parentid
      where bd09_lng is not null
        and bd09_lat is not null);"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${LOAD_DATA_DIM_PROVINCE}"